package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public  class SectionImpl implements SectionDao {
	private Connection conn;
	private PreparedStatement sectionQuery;
	private ResultSet rs;
	@Override
	public int add(Section sectionbean) {
		int result=0;
		try {
		conn=DBcon.getConnection();
		String sql="insert into section (id,name,type,phone,fax,tsection,ftime,des) values(?,?,?,?,?,?,?,?)";
		sectionQuery=conn.prepareStatement(sql);
		sectionQuery.setString(1,sectionbean.getId());
		sectionQuery.setString(2,sectionbean.getName());
		sectionQuery.setString(3,sectionbean.getType());
		sectionQuery.setString(4,sectionbean.getPhone());
		sectionQuery.setString(5,sectionbean.getFax());
		sectionQuery.setString(6,sectionbean.getTsection());
		sectionQuery.setString(7, sectionbean.getFtime());
		sectionQuery.setString(8,sectionbean.getDes());
		result=sectionQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(sectionQuery);
		}
		return result;
	}

	@Override
	public int delete(String id) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="delete from section where id='"+id+"'";
			sectionQuery=conn.prepareStatement(sql);
			result=sectionQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(sectionQuery);
		}
		return result;
	}

	@Override
	public int update(Section sectionbean) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="update section set name=?,type=?,phone=?,fax=?,tsection=?,ftime=?,des=? where id=?";
			sectionQuery=conn.prepareStatement(sql);
			sectionQuery.setString(1,sectionbean.getName());
			sectionQuery.setString(2,sectionbean.getType());
			sectionQuery.setString(3, sectionbean.getPhone());
			sectionQuery.setString(4,sectionbean.getFax());
			sectionQuery.setString(5,sectionbean.getTsection());
			sectionQuery.setString(6,sectionbean.getFtime());
			sectionQuery.setString(7,sectionbean.getDes());
			sectionQuery.setString(8,sectionbean.getId());
			result=sectionQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(sectionQuery);
		}
		return result;
		
	}

	@Override
	public List<Section> find(String id,String name,String type){
		List<Section> sectionList=new ArrayList<Section>();
		try {
			conn=DBcon.getConnection();
			String sql="select id,name,type,phone,fax,tsection,ftime,des from section where id='"+id+"' or name='"+name+"' or type='"+type+"'";
			sectionQuery=conn.prepareStatement(sql);
			rs=sectionQuery.executeQuery();
			while(rs.next()) {
				Section section=new Section();
				section.setId(rs.getString("id"));
				section.setName(rs.getString("name"));
				section.setType(rs.getString("type"));
				section.setPhone(rs.getString("phone"));
				section.setFax(rs.getString("fax"));
				section.setTsection(rs.getString("tsection"));
				section.setFtime(rs.getString("ftime"));
				section.setDes(rs.getString("des"));
				sectionList.add(section);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(sectionQuery);
		}
		
		return sectionList;

	}

	@Override
	public Section findById(String id) {
		Section section=null;
		
		try {
			conn=DBcon.getConnection();
			String sql="select * from section where id='"+id+"'";
			sectionQuery=conn.prepareStatement(sql);
			rs=sectionQuery.executeQuery();
			while(rs.next()) {
				section=new Section();
				section.setId(rs.getString("id"));
				section.setName(rs.getString("name"));
				section.setType(rs.getString("type"));
				section.setPhone(rs.getString("phone"));
				section.setFax(rs.getString("fax"));
				section.setTsection(rs.getString("tsection"));
				section.setFtime(rs.getString("ftime"));
				section.setDes(rs.getString("des"));
				
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(sectionQuery);
		}
		
		return section;
		
	}

}
